import sqlite3
import json
import os
from datetime import datetime
import json

class Database:
    """
    数据库操作类，使用SQLite实现数据持久化存储
    提供操场开放时间、跑步记录等数据的存储和查询功能
    """
    
    def __init__(self, db_path=None):
        self.db_path = db_path or os.path.join(
            os.path.dirname(__file__), '..', 'data', 'running_tool.db')
        self._ensure_db_exists()
    
    def _ensure_db_exists(self):
        """确保数据库和表结构存在"""
        # 确保数据目录存在
        os.makedirs(os.path.dirname(self.db_path), exist_ok=True)
        
        # 创建表结构
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 操场开放时间表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS sport_field_hours (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                day_of_week TEXT NOT NULL,
                start_time TEXT,
                end_time TEXT,
                note TEXT,
                crawl_time TIMESTAMP,
                is_active BOOLEAN DEFAULT 1,
                UNIQUE(day_of_week, start_time, end_time)
            )
            ''')
            
            # 跑步路线表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS running_routes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                distance REAL NOT NULL,
                elevation_gain REAL DEFAULT 0,
                difficulty TEXT DEFAULT '中等',
                description TEXT,
                path_data TEXT NOT NULL,  -- JSON格式存储路径坐标
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
            ''')
            
            # 跑步记录表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS running_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                route_id INTEGER,
                distance REAL,
                duration INTEGER,  -- 秒
                start_time TIMESTAMP,
                end_time TIMESTAMP,
                notes TEXT,
                weather_data TEXT,  -- JSON格式存储天气数据
                FOREIGN KEY (route_id) REFERENCES running_routes(id)
            )
            ''')
            
            # 天气记录表
            cursor.execute('''
            CREATE TABLE IF NOT EXISTS weather_records (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                temperature REAL,
                feels_like REAL,
                humidity INTEGER,
                wind_speed REAL,
                weather TEXT,
                weather_icon TEXT,
                is_raining BOOLEAN DEFAULT 0,
                rain_amount REAL DEFAULT 0
            )
            ''')
            
            conn.commit()
    
    def save_open_hours(self, open_hours_data, crawl_time=None):
        """保存操场开放时间数据"""
        if crawl_time is None:
            crawl_time = datetime.now()
        
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 先将当前数据标记为非活动
            cursor.execute('UPDATE sport_field_hours SET is_active = 0')
            
            # 插入新数据
            for day, data in open_hours_data.items():
                for slot in data.get('time_slots', []):
                    cursor.execute('''
                    INSERT OR REPLACE INTO sport_field_hours 
                    (day_of_week, start_time, end_time, note, crawl_time, is_active)
                    VALUES (?, ?, ?, ?, ?, 1)
                    ''', (day, slot['start'], slot['end'], data.get('note', ''), crawl_time))
            
            conn.commit()
            return True
    
    def get_active_open_hours(self):
        """获取当前有效的开放时间数据"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute('''
            SELECT day_of_week, start_time, end_time, note 
            FROM sport_field_hours 
            WHERE is_active = 1
            ORDER BY day_of_week
            ''')
            
            rows = cursor.fetchall()
            
            # 按星期几整理数据
            open_hours = {}
            for row in rows:
                day = row['day_of_week']
                if day not in open_hours:
                    open_hours[day] = {
                        'time_slots': [],
                        'note': row['note']
                    }
                
                open_hours[day]['time_slots'].append({
                    'start': row['start_time'],
                    'end': row['end_time']
                })
            
            return open_hours
    
    def save_route(self, route_data):
        """保存跑步路线数据"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            # 检查路线是否已存在
            if 'id' in route_data and route_data['id']:
                # 更新现有路线
                cursor.execute('''
                UPDATE running_routes
                SET name = ?, distance = ?, elevation_gain = ?, 
                    difficulty = ?, description = ?, path_data = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                ''', (
                    route_data['name'],
                    route_data['distance'],
                    route_data.get('elevation_gain', 0),
                    route_data.get('difficulty', '中等'),
                    route_data.get('description', ''),
                    json.dumps(route_data['path']),
                    route_data['id']
                ))
                return route_data['id']
            else:
                # 插入新路线
                cursor.execute('''
                INSERT INTO running_routes 
                (name, distance, elevation_gain, difficulty, description, path_data)
                VALUES (?, ?, ?, ?, ?, ?)
                ''', (
                    route_data['name'],
                    route_data['distance'],
                    route_data.get('elevation_gain', 0),
                    route_data.get('difficulty', '中等'),
                    route_data.get('description', ''),
                    json.dumps(route_data['path'])
                ))
                return cursor.lastrowid
    
    def save_routes(self, routes_data):
        """批量保存跑步路线数据"""
        with sqlite3.connect(self.db_path) as conn:
            for route in routes_data:
                self.save_route(route)
            conn.commit()
        return True
    
    def get_all_routes(self):
        """获取所有跑步路线"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute('''
            SELECT id, name, distance, elevation_gain, difficulty, description, path_data
            FROM running_routes
            ORDER BY id
            ''')
            
            routes = []
            for row in cursor.fetchall():
                route = dict(row)
                route['path'] = json.loads(route.pop('path_data'))
                routes.append(route)
            
            return routes
    
    def get_route_by_id(self, route_id):
        """根据ID获取路线"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute('''
            SELECT id, name, distance, elevation_gain, difficulty, description, path_data
            FROM running_routes
            WHERE id = ?
            ''', (route_id,))
            
            row = cursor.fetchone()
            if row:
                route = dict(row)
                route['path'] = json.loads(route.pop('path_data'))
                return route
            return None
    
    def save_running_record(self, record_data):
        """保存跑步记录"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
            INSERT INTO running_records 
            (route_id, distance, duration, start_time, end_time, notes, weather_data)
            VALUES (?, ?, ?, ?, ?, ?, ?)
            ''', (
                record_data.get('route_id'),
                record_data.get('distance'),
                record_data.get('duration'),
                record_data.get('start_time'),
                record_data.get('end_time'),
                record_data.get('notes', ''),
                json.dumps(record_data.get('weather_data', {})) if record_data.get('weather_data') else None
            ))
            
            conn.commit()
            return cursor.lastrowid
    
    def get_running_records(self, limit=None):
        """获取跑步记录"""
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            query = '''
            SELECT id, route_id, distance, duration, start_time, end_time, notes, weather_data
            FROM running_records
            ORDER BY start_time DESC
            '''
            
            if limit:
                query += f" LIMIT {limit}"
            
            cursor.execute(query)
            
            records = []
            for row in cursor.fetchall():
                record = dict(row)
                if record['weather_data']:
                    record['weather_data'] = json.loads(record['weather_data'])
                records.append(record)
            
            return records
    
    def save_weather_record(self, weather_data):
        """保存天气记录"""
        with sqlite3.connect(self.db_path) as conn:
            cursor = conn.cursor()
            
            cursor.execute('''
            INSERT INTO weather_records 
            (timestamp, temperature, feels_like, humidity, wind_speed, 
             weather, weather_icon, is_raining, rain_amount)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                weather_data.get('timestamp') or datetime.now(),
                weather_data.get('temperature'),
                weather_data.get('feels_like'),
                weather_data.get('humidity'),
                weather_data.get('wind_speed'),
                weather_data.get('weather'),
                weather_data.get('weather_icon'),
                1 if weather_data.get('is_raining') else 0,
                weather_data.get('rain', 0)
            ))
            
            conn.commit()
            return cursor.lastrowid
    
    def export_to_json(self, table_name, output_path):
        """将表数据导出为JSON文件"""
        valid_tables = ['sport_field_hours', 'running_routes', 'running_records', 'weather_records']
        
        if table_name not in valid_tables:
            raise ValueError(f"无效的表名，有效表名: {', '.join(valid_tables)}")
        
        with sqlite3.connect(self.db_path) as conn:
            conn.row_factory = sqlite3.Row
            cursor = conn.cursor()
            
            cursor.execute(f'SELECT * FROM {table_name}')
            rows = cursor.fetchall()
            
            # 转换为字典列表
            data = [dict(row) for row in rows]
            
            # 处理特殊字段
            if table_name == 'running_routes':
                for item in data:
                    if 'path_data' in item and item['path_data']:
                        item['path'] = json.loads(item.pop('path_data'))
            
            elif table_name == 'running_records':
                for item in data:
                    if 'weather_data' in item and item['weather_data']:
                        item['weather_data'] = json.loads(item['weather_data'])
            
            # 保存到JSON文件
            with open(output_path, 'w', encoding='utf-8') as f:
                json.dump(data, f, ensure_ascii=False, indent=2, default=str)
            
            return True
    
    def import_from_json(self, table_name, json_path):
        """从JSON文件导入数据到表"""
        # 此功能可以根据需要实现
        pass


class StorageManager:
    """
    存储管理器，集成JSON和数据库存储，提供统一的接口
    """
    
    def __init__(self, use_database=True):
        self.use_database = use_database
        self.database = Database() if use_database else None
        self.data_dir = os.path.join(os.path.dirname(__file__), '..', 'data')
    
    def save_open_hours(self, data, crawl_time=None):
        """保存开放时间数据"""
        if self.use_database:
            # 保存到数据库
            self.database.save_open_hours(data, crawl_time)
        
        # 同时保存为JSON文件（备份）
        output_path = os.path.join(self.data_dir, 'open_hours.json')
        result = {
            "crawl_time": crawl_time or datetime.now().strftime("%Y-%m-%d %H:%M:%S"),
            "open_hours": data
        }
        
        with open(output_path, 'w', encoding='utf-8') as f:
            json.dump(result, f, ensure_ascii=False, indent=2)
        
        return True
    
    def get_open_hours(self):
        """获取开放时间数据"""
        if self.use_database:
            # 从数据库获取
            db_data = self.database.get_active_open_hours()
            if db_data:
                return db_data
        
        # 从JSON文件获取
        json_path = os.path.join(self.data_dir, 'open_hours.json')
        if os.path.exists(json_path):
            try:
                with open(json_path, 'r', encoding='utf-8') as f:
                    data = json.load(f)
                    return data.get('open_hours', {})
            except Exception:
                pass
        
        return {}
    
    def save_routes(self, routes_data):
        """保存路线数据"""
        if self.use_database:
            # 保存到数据库
            self.database.save_routes(routes_data)
        
        # 同时保存为JSON文件（备份）
        output_path = os.path.join(self.data_dir, 'routes.json')
        with open(output_path, 'w', encoding='utf-8') as f:
            json.dump(routes_data, f, ensure_ascii=False, indent=2)
        
        return True
    
    def get_routes(self):
        """获取路线数据"""
        if self.use_database:
            # 从数据库获取
            db_data = self.database.get_all_routes()
            if db_data:
                return db_data
        
        # 从JSON文件获取
        json_path = os.path.join(self.data_dir, 'routes.json')
        if os.path.exists(json_path):
            try:
                with open(json_path, 'r', encoding='utf-8') as f:
                    return json.load(f)
            except Exception:
                pass
        
        # 返回默认路线
        from src.data_handler import DataHandler
        return DataHandler()._get_default_routes()
    
    def save_running_record(self, record_data):
        """保存跑步记录"""
        if self.use_database:
            return self.database.save_running_record(record_data)
        
        # 如果不使用数据库，返回None
        return None
    
    def export_data(self, table_name, output_path):
        """导出数据"""
        if self.use_database:
            return self.database.export_to_json(table_name, output_path)
        return False


if __name__ == "__main__":
    # 示例使用
    storage = StorageManager()
    
    # 测试获取默认路线
    routes = storage.get_routes()
    print(f"获取到 {len(routes)} 条跑步路线")
    
    # 保存路线到数据库
    storage.save_routes(routes)
    print("路线数据已保存到数据库")
    
    # 导出数据到JSON
    output_dir = os.path.join(os.path.dirname(__file__), '..', 'data', 'exports')
    os.makedirs(output_dir, exist_ok=True)
    
    storage.database.export_to_json('running_routes', 
                                  os.path.join(output_dir, 'routes_export.json'))
    print("数据已导出到JSON文件")